package code.service;

import code.entity.Location;
import code.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class LocationService {
    public static List<Location> getLocationList(String date, String username) throws SQLException {
        String sql = "select * from location where createTime >=?  and createTime<=? and username=?";

        Connection con = DBUtil.getConn(true);
        PreparedStatement psmt = con.prepareStatement(sql);
        psmt.setString(1, date + " 00:00:00");
        psmt.setString(2, date + " 23:59:59");
        psmt.setString(3, username);

        return rs2list(psmt.executeQuery());
    }


    public static List<Location> getNeedConvertLocationList() throws SQLException {
        String sql = "select * from location where x is null or x=''";

        Connection con = DBUtil.getConn(true);
        PreparedStatement psmt = con.prepareStatement(sql);

        return rs2list(psmt.executeQuery());
    }


    public static List<Location> rs2list(ResultSet rs) throws SQLException {
        List<Location> list = new ArrayList<Location>();
        while (rs.next()) {
            Location l = new Location();
            l.setId(rs.getInt("id"));
            l.setLat(rs.getString("lat"));
            l.setLng(rs.getString("lng"));
            l.setUsername(rs.getString("username"));
            l.setCreateTime(rs.getTimestamp("createTime"));
            l.setX(rs.getString("x"));
            l.setY(rs.getString("y"));
            list.add(l);
        }

        return list;
    }


    public static void saveLocation(Connection con,Location location) throws SQLException {

        String sql = "insert into location (id,lng,lat,username,createTime) values(?,?,?,?,?)";

        PreparedStatement psmt = con.prepareStatement(sql);
        psmt.setInt(1, 0);
        psmt.setString(2, location.getLng());
        psmt.setString(3, location.getLat());
        psmt.setString(4, location.getUsername());
        psmt.setTimestamp(5, new Timestamp(new Date().getTime()));
        psmt.executeUpdate();
    }


    public static void updateLocation(Connection con,Integer id, String x, String y) throws SQLException {
        String sql = "update location set x=?,y=? where id=?";

        PreparedStatement psmt = con.prepareStatement(sql);
        psmt.setString(1, x);
        psmt.setString(2, y);
        psmt.setInt(3, id);
        psmt.executeUpdate();
    }


}

